﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using DB.SqlHelper;
using Framework.Common.Common;
using Mode;

namespace Framework.DataAccess
{
    /// <summary>
    /// 题库
    /// </summary>
    public class Topic
    {
        private DbInstance db = null;
        public Topic()
        {
            db = DbManager.GetDbInstance();
        }

        public Topic(bool HasTrans)
        { 
            if(!HasTrans)
                db = DbManager.GetDbInstance();
        }

        /// <summary>
        /// 获取题型对应的角色
        /// </summary>
        /// <returns></returns>
        public DataView GetTopicTypeRoleList()
        {
            string strSql = "SELECT T_TYPE,R_ID,R_NAME FROM topic_view GROUP BY T_TYPE,R_ID,R_NAME";
            DataTable table = db.ExecuteDataTableBySql(strSql);
            return table.DefaultView;
        }

        /// <summary>
        /// 查询题目
        /// </summary>
        /// <param name="pageInfo"></param>
        /// <param name="type">题型</param>
        /// <param name="roleID">角色ID</param>
        /// <param name="beginDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="title">题目名称</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        public PageList<object> GetTopicList(PageView pageInfo, int status, int? type = null, int? roleID = null, DateTime? beginDate = null, DateTime? endDate = null, string title = "")
        {
            string strWhere = " WHERE 1=1 ";
            if (type.HasValue)
                if (type.Value > 0)
                    strWhere += string.Format(" And type={0}", type.Value);
            if (roleID.HasValue)
                if (roleID.Value > 0)
                    strWhere += string.Format(" And [id] IN(Select topic From topic_role_join Where role={0})", roleID.Value);
            if (beginDate.HasValue)
                strWhere += string.Format(" And [date]>='{0}'", beginDate.Value.ToString("yyyy-MM-dd"));
            if (endDate.HasValue)
                strWhere += string.Format(" And [date]<='{0} 23:59'", endDate.Value.ToString("yyyy-MM-dd"));
            if (title !=null && title.Length > 0)
                strWhere += string.Format(" And title Like '%{0}%'", title.Replace("'", "''"));
            if (status == 0 || status == 1)
                strWhere += string.Format(" And status={0}", status);

            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  COUNT(*) as Row FROM  topic_table " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By [id] ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where rows Between {4} And {5}";
            strSql = string.Format(strSql, orderby, "*", "topic_table", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);


            list.Total = rowCount;
            list.Data = dt;

            return list;
        }

        /// <summary>
        /// 修改题目的停用/启用状态
        /// </summary>
        /// <param name="topicID"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public bool SetTopicStatus(int topicID, int status)
        {
            string strSql = string.Format("Update topic_table Set status={0} Where [id]={1}", status, topicID);
            int count = db.ExecuteNonQueryBySql(strSql);
            return count == 1;
        }

        /// <summary>
        /// 增加题目
        /// </summary>
        /// <param name="topic">题目数据</param>
        /// <param name="anserList">题目选项及答案列表</param>
        /// <param name="roleList">角色列表</param>
        /// <returns></returns>
        public int AddTopic(topic_table topic, List<answer_table> anserList, List<int> roleList)
        {
            string strSql = "";
            DbManager.BeginTransaction();
            db = DbManager.GetDbInstance();
            try
            {
                //增加主表
                strSql = "INSERT INTO topic_table([title],[type],[date],[status])Values(@title,{0},'{1}',{2});Select Scope_Identity()As topicID";
                strSql = string.Format(strSql, topic.type, topic.date.ToString("yyyy-MM-dd"), topic.status);
                DbCommand cmd = db.GetSqlStringCommand(strSql);
                db.AddInParameter(cmd, "@title", DbType.String, topic.title);
                object obj = db.ExecuteScalar(cmd);
                if (obj == null)
                {
                    DbManager.Rollback();
                    return 0;
                }
                int topicID = Convert.ToInt32(obj);
                //增加子表
                foreach (answer_table anser in anserList)
                {
                    anser.topic = topicID;
                    AddTopicAnser(anser);
                }
                //增加角色列表
                foreach (int role in roleList)
                {
                    strSql = string.Format("Insert Into topic_role_join(role,topic)Values({0},{1});", role, topicID);
                    db.ExecuteNonQueryBySql(strSql);
                }
                DbManager.Commit();
                return topicID;
            }
            catch (Exception e)
            {
                DbManager.Rollback();
                throw e;
            }
        }

        /// <summary>
        /// 增加题目答案表
        /// </summary>
        /// <param name="anser"></param>
        private int AddTopicAnser(answer_table anser)
        {
            string strSql = "Insert Into answer_table(answer,[right],code,[date],topic)Values(@answer,{0},{1},'{2}',{3});Select Scope_Identity()As anserID;";
            strSql = string.Format(strSql, anser.right, anser.code, anser.date.ToString("yyyy-MM-dd"), anser.topic);
            DbCommand childCmd = db.GetSqlStringCommand(strSql);
            db.AddInParameter(childCmd, "@answer", DbType.String, anser.answer);
            object obj = db.ExecuteScalar(childCmd);
            if (obj != null)
                return obj.ToInt(0);
            else
                return 0;
        }

        /// <summary>
        /// 修改题目
        /// </summary>
        /// <param name="topic">题目数据</param>
        /// <param name="anserList">题目选项及答案列表</param>
        /// <param name="roleList">角色列表</param>
        /// <returns></returns>
        public bool UpdateTopic(topic_table topic, List<answer_table> anserList, List<int> roleList)
        {
            string strSql = "";
            DbManager.BeginTransaction();
            db = DbManager.GetDbInstance();
            try
            {
                //修改主表
                strSql = "Update topic_table Set [title]=@title,[type]={0},[date]='{1}',[status]={2} Where [id]={3}";
                strSql = string.Format(strSql, topic.type, topic.date.ToString("yyyy-MM-dd"), topic.status, topic.id);
                DbCommand cmd = db.GetSqlStringCommand(strSql);
                db.AddInParameter(cmd, "@title", DbType.String, topic.title);
                int count = db.ExecuteNonQuery(cmd);
                //修改子表
                string strIDs = "";
                foreach (answer_table anser in anserList)
                {
                    if (anser.id == 0)
                    {
                        int id = AddTopicAnser(anser);
                        strIDs += id + ",";
                    }
                    else
                    {
                        UpdateTopicAnser(anser);
                        strIDs += anser.id + ",";
                    }   
                }
                //删除（不允许删除）
                //DelAnaserDets(topic.id, strIDs);

                //角色
                strSql = "";
                foreach (int roleID in roleList)
                {
                    strSql += string.Format("If Not Exists(Select role From topic_role_join Where topic={0} And role={1}) Insert Into topic_role_join(topic,role)Values({0},{1});\r\n", topic.id, roleID);
                }
                if (strSql.Length > 0)
                    db.ExecuteNonQueryBySql(strSql);
                //提交事务
                DbManager.Commit();
                return count >= 1;
            }
            catch (Exception e)
            {
                DbManager.Rollback();
                throw e;
            }
        }

        /// <summary>
        /// 修改题目答案表
        /// </summary>
        /// <param name="anser"></param>
        private void UpdateTopicAnser(answer_table anser)
        {
            string strSql = "Update answer_table Set answer=@answer,[right]={0},code={1},[date]='{2}',topic={3} Where [id]={4}";
            strSql = string.Format(strSql, anser.right, anser.code, anser.date.ToString("yyyy-MM-dd"), anser.topic, anser.id);
            DbCommand childCmd = db.GetSqlStringCommand(strSql);
            db.AddInParameter(childCmd, "@answer", DbType.String, anser.answer);
            db.ExecuteNonQuery(childCmd);
        }

        /// <summary>
        /// 删除指定题目中除需要保存外的答案明细
        /// </summary>
        /// <param name="topicID"></param>
        /// <param name="saveIDs"></param>
        /// <returns></returns>
        private bool DelAnaserDets(int topicID, string saveIDs)
        {
            saveIDs = saveIDs.TrimEnd(',').Trim();
            if (saveIDs.Length == 0 || topicID == 0)
                return true;
            string strSql = string.Format("Delete answer_table Where topic={0} And [id] Not In({0})", topicID, saveIDs);
            int rowCount = db.ExecuteNonQueryBySql(strSql).ToInt();
            return rowCount > 0;
        }

        /// <summary>
        /// 根据ID获取一条题目数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public topic_table GetTopicByID(int id)
        {
            string strSql = "Select * From topic_table Where id=" + id;
            IDataReader reader = db.ExecuteReaderBySql(strSql);
            IList<topic_table> list = DbManager.FillList<topic_table>(reader);
            if (!reader.IsClosed)
                reader.Close();
            if (list.Count > 0)
                return list[0];
            else
                return null;
        }

        /// <summary>
        /// 根据题目ID获取选项列表
        /// </summary>
        /// <param name="topicID"></param>
        /// <returns></returns>
        public IList<answer_table> GetAnserListByID(int topicID)
        {
            string strSql = "Select * From answer_table Where topic=" + topicID;
            IDataReader reader = db.ExecuteReaderBySql(strSql);
            IList<answer_table> list = DbManager.FillList<answer_table>(reader);
            if (!reader.IsClosed)
                reader.Close();
            return list;
        }

        /// <summary>
        /// 根据题目ID获取角色列表
        /// </summary>
        /// <param name="topicID"></param>
        /// <returns></returns>
        public IList<int> GetTopicRoleList(int topicID)
        {
            string strSql = "Select role From topic_role_join Where topic=" + topicID;
            IDataReader reader = db.ExecuteReaderBySql(strSql);
            IList<int> roleList = new List<int>();
            while (reader.Read())
                roleList.Add(reader["role"].ToInt(0));
            if (!reader.IsClosed)
                reader.Close();
            return roleList;
        }

        /// <summary>
        /// 根据角色类型或者角色ID获取题目数量
        /// </summary>
        /// <param name="roleType">0:根据角色ID，1：公共角色</param>
        /// <param name="roleID"></param>
        /// <param name="topicType">题目类型</param>
        /// <returns></returns>
        public int CountRoleTopic(int roleType, int roleID, int? topicType)
        {
            string strSql = "Select COUNT(*) From topic_view Where T_STATUS=0 ";
            if (roleType == 0)
                strSql += " And R_ID=" + roleID;
            else if (roleType == 1)
                strSql += " And R_POWER=2";
            if (topicType.HasValue)
                strSql += " And T_TYPE=" + topicType;
            int count = db.ExecuteScalarBySql(strSql).ToInt();
            return count;
        }

        /// <summary>
        /// 根据角色题型获取题目列表
        /// </summary>
        /// <param name="pageInfo"></param>
        /// <param name="type"></param>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public PageList<object> GetModelRoleTopicList(PageView pageInfo, int type, int roleID)
        {
            string strWhere = string.Format(" WHERE T_STATUS=0 And [T_TYPE]={0} And (R_ID={1} Or R_POWER=2)", type, roleID);

            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  COUNT(*) as Row FROM  topic_view " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);

            string orderby = " Order By [T_ID] ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where rows Between {4} And {5}";
            strSql = string.Format(strSql, orderby, pageInfo.Fields, "topic_view", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);


            list.Total = rowCount;
            list.Data = dt;

            return list;
        }
    }
}
